from data_access.read_db import execute_select
import os


def main():
    # Define the reverse mapping dictionary (abbreviation -> full word)
    full_word_mapping = {
        'RD': 'ROAD',
        'AVE': 'AVENUE',
        'BT': 'BUKIT',
        'BLVD': 'BOULEVARD',
        'CL': 'CLOSE',
        'CTRL': 'CENTRAL',
        'CRES': 'CRESCENT',
        'DR': 'DRIVE',
        'JLN': 'JALAN',
        'KG': 'KAMPONG',
        'LN': 'LANE',
        'LOR': 'LORONG',
        'UPP': 'UPPER',
        'PL': 'PLACE',
        'ST': 'STREET',
        'TG': 'TANJONG',
        'NTH': 'NORTH',
        'STH': 'SOUTH',
        'CTR': 'CENTRE'
    }

    # First, fetch all records that need updating
    records_df = execute_select("""
        SELECT month, planarea, flat_type, blk_no, street, storey_range, 
               floor_area_sqm, flat_model, lease_commence_date, resale_price 
        FROM resale_flat_prices
    """)

    total_records = len(records_df)
    print(f"Total records to process: {total_records}")

    # Create a SQL file for batch inserts
    sql_filename = "insert_all_records_with_expanded_street_names.sql"
    insert_count = 0

    with open(sql_filename, 'w') as sql_file:
        # Write a header comment
        sql_file.write("-- Batch insert of all records with expanded street names where applicable\n")
        sql_file.write("-- Generated by street_name_updater.py\n\n")

        # Begin transaction for atomic execution
        sql_file.write("BEGIN;\n\n")

        for index, record in records_df.iterrows():
            # Show progress every 1000 records
            if index % 1000 == 0 or index == total_records - 1:
                print(f"Processing record {index + 1} of {total_records} ({((index + 1) / total_records) * 100:.1f}%)")

            # Access fields
            month = record['month']
            planarea = record['planarea']
            flat_type = record['flat_type']
            blk_no = record['blk_no']
            street = record['street']
            storey_range = record['storey_range']
            floor_area_sqm = record['floor_area_sqm']
            flat_model = record['flat_model']
            lease_commence_date = record['lease_commence_date']
            resale_price = record['resale_price']

            # Initialize updated_street with original value
            updated_street = street

            # Process street name if it's a string
            if isinstance(street, str):
                # Split the street into parts and process each part
                parts = street.split()
                updated_parts = []

                for part in parts:
                    # Convert to uppercase for comparison
                    upper_part = part.upper()
                    # Check if the part is an abbreviation we need to expand
                    if upper_part in full_word_mapping:
                        # Get the full word and apply original capitalization
                        full_word = full_word_mapping[upper_part]
                        # Preserve the original capitalization if it was all caps
                        if part == upper_part:
                            updated_parts.append(full_word)
                        else:
                            updated_parts.append(full_word.capitalize())
                    else:
                        updated_parts.append(part)

                updated_street = ' '.join(updated_parts)

            # Create the SQL insert statement with proper escaping
            sql = f"""
INSERT INTO resale_flat_prices 
(month, planarea, flat_type, blk_no, street, storey_range, floor_area_sqm, flat_model, lease_commence_date, resale_price)
VALUES (
    '{month}', 
    '{planarea.replace("'", "''")}', 
    '{flat_type.replace("'", "''")}', 
    '{blk_no.replace("'", "''")}', 
    '{updated_street.replace("'", "''") if isinstance(updated_street, str) else updated_street}', 
    '{storey_range.replace("'", "''")}', 
    {floor_area_sqm}, 
    '{flat_model.replace("'", "''")}', 
    {lease_commence_date}, 
    {resale_price}
);\n"""

            sql_file.write(sql)
            insert_count += 1

        # Commit the transaction
        sql_file.write("\nCOMMIT;\n")

    print(f"\nProcessing complete! Generated SQL file: {sql_filename}")
    print(f"Total records prepared: {insert_count}")
    print(f"\nTo execute these inserts, run the SQL file with a command like:")
    print(f"psql -U your_username -d your_database -f {sql_filename}")


if __name__ == "__main__":
    main()
